using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class OpLenhSXDataAccess : Connection
    {
        #region Local Variable
        public OpLenhSXDataAccess(string type, string connectString, int timeout = 0) : base(type, connectString, timeout) { }
        public OpLenhSXDataAccess(Connection connection) : base(connection) { }
        #endregion Local Variable

        #region Method


        /* Can not combine this 		public DataTable GetByFilterToDataTable(<%HList(<%,%><% %><%Type%> <%AccountColumn(Name)%>)%>*/
        public DataTable GetByFilterToDataTable(List<DTO.FilterParameter> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0)
        {
            string sql = @"SELECT  LenhSX, Name, Status
            FROM  OpLenhSX";

            bool hasfilters = BeginFilterParameter(ref sql, filters, "WHERE");

            sql += @" ORDER BY  LenhSX, Name";

            if (itemPerPage != 0)
            {
                if (DbType == "S") sql += @" OFFSET @indexPage ROWS FETCH NEXT @itemPerPage ROWS ONLY";
                else sql += @" LIMIT @indexPage ,@itemPerPage ";
            }

            InitConnect();
            InitCommand(sql);
            /* Can not combine this             AddParameter(OpLenhSXInfo.Field.<%AccountColumn(Name)%>, <%AccountColumn(Name)%>);*/
            if (itemPerPage != 0)
            {
                AddParameter("indexPage", indexPage);
                AddParameter("itemPerPage", itemPerPage);
            }

            if (hasfilters) AddFilterParameter(filters);

            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }

            return list;
        }


        public OpLenhSXInfo[] GetByFilter(List<DTO.FilterParameter> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0)
        {
            DataTable list = GetByFilterToDataTable(filters, ref sErr);

            if (sErr != "") return null;

            OpLenhSXInfo[] res = null;

            try
            {
                res = new OpLenhSXInfo[list.Rows.Count];
                for (int i = 0; i < list.Rows.Count; i++)
                    res[i] = new OpLenhSXInfo(list.Rows[i]);
            }
            catch (Exception ex) { sErr = ex.Message; }

            if (sErr != "") return null;
            return res;
        }

        /* Can not combine this 		public int GetCountRecord(<%HList(<%,%><% %><%Type%> <%AccountColumn(Name)%>)%>*/
        public int GetCountRecord(List<DTO.FilterParameter> filters, ref string sErr)
        {
            string sql = @"SELECT COUNT(*)
                FROM  OpLenhSX";

            bool hasfilters = BeginFilterParameter(ref sql, filters, "WHERE");

            int ret = -1;
            openConnection();
            InitCommand(sql);
            /* Can not combine this             AddParameter(OpLenhSXInfo.Field.<%AccountColumn(Name)%>, <%AccountColumn(Name)%>);*/

            if (hasfilters) AddFilterParameter(filters);

            try
            {
                object tmp = executeScalar();

                if (tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
                else ret = 0;
            }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return ret;
        }

        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public int Add(OpLenhSXInfo objEntr, ref string sErr)
        {
            string sqlFields = @" INSERT INTO OpLenhSX(LenhSX,Name,";
            string sqlValues = @" VALUES(@LenhSX,@Name,";

            if (objEntr.Status != null) { sqlFields += " Status,"; sqlValues += " @Status,"; }
            /* Can not combine this 			if (objEntr.<%CreateColumn(Name)%> != null) { sqlFields += " <%CreateColumn(FieldName)%>,"; sqlValues += " @<%CreateColumn(FieldName)%>,"; }*/
            /* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) { sqlFields += " <%UpdateColumn(FieldName)%>,"; sqlValues += " @<%UpdateColumn(FieldName)%>,"; }*/

            sqlFields = sqlFields.Substring(0, sqlFields.Length - 1) + ")";
            sqlValues = sqlValues.Substring(0, sqlValues.Length - 1) + ")";

            int ret = -1;
            string sql = sqlFields + sqlValues;
            openConnection();
            InitCommand(sql);
            AddParameter(OpLenhSXInfo.Field.LenhSX, objEntr.LenhSX);
            AddParameter(OpLenhSXInfo.Field.Name, objEntr.Name);

            if (objEntr.Status != null) AddParameter(OpLenhSXInfo.Field.Status, objEntr.Status);
            /* Can not combine this 			if (objEntr.<%CreateColumn(Name)%> != null) AddParameter(OpLenhSXInfo.Field.<%CreateColumn(Name)%>, objEntr.<%CreateColumn(Name)%>);*/
            /* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) AddParameter(OpLenhSXInfo.Field.<%UpdateColumn(Name)%>, objEntr.<%UpdateColumn(Name)%>);*/

            try
            {
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
                else ret = 0;
            }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return ret;
        }

        public string Update(OpLenhSXInfo objEntr)
        {
            string sql = @"UPDATE OpLenhSX SET ";

            if (objEntr.Status != null) { sql += "Status = @Status,"; }
            /* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) { sql += "<%UpdateColumn(FieldName)%> = @<%UpdateColumn(FieldName)%>,"; }*/

            sql = sql.Substring(0, sql.Length - 1);
            sql += @" WHERE  LenhSX = @LenhSX AND Name = @Name";

            openConnection();
            InitCommand(sql);

            AddParameter(OpLenhSXInfo.Field.LenhSX, objEntr.LenhSX);
            AddParameter(OpLenhSXInfo.Field.Name, objEntr.Name);

            if (objEntr.Status != null) AddParameter(OpLenhSXInfo.Field.Status, objEntr.Status);
            /* Can not combine this 			if (objEntr.<%UpdateColumn(Name)%> != null) AddParameter(OpLenhSXInfo.Field.<%UpdateColumn(Name)%>, objEntr.<%UpdateColumn(Name)%>);          */

            string sErr = "";

            try { executeNonQuery(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return sErr;
        }

        public string Delete(String LenhSX, String Name)
        {
            string sql = @"DELETE OpLenhSX
            WHERE 	 LenhSX = @LenhSX AND Name = @Name";

            openConnection();
            InitCommand(sql);

            AddParameter(OpLenhSXInfo.Field.LenhSX, LenhSX);
            AddParameter(OpLenhSXInfo.Field.Name, Name);

            string sErr = "";
            try { executeNonQuery(); }
            catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
            finally { closeConnection(); }

            return sErr;
        }

        public Boolean IsExist(String LenhSX, String Name, ref string sErr)
        {
            string sql = @"SELECT TOP (1) LenhSX, Name
            FROM  OpLenhSX
            WHERE 	 LenhSX = @LenhSX AND Name = @Name";

            InitConnect();
            InitCommand(sql);
            AddParameter(OpLenhSXInfo.Field.LenhSX, LenhSX);
            AddParameter(OpLenhSXInfo.Field.Name, Name);

            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message + " sql='" + sql + "'"; }

            return list.Rows.Count == 1;
        }

        #endregion Method

    }
}
